#include "databaseclass.h"
#include <QDebug>
#include <QDateTime>
#include "systemfile.h"
databaseClass::databaseClass()
{
    databaseinit("tenement_passing_voice.db");
    QString ip = sysInfo->serverIp;
    int port =sysInfo->serverPort.toInt();
    //QString user = "xyyj_jh_copy";
    //QString password = "gzxx_xyyj_copy";
    //QString dateBaseName = "bankclearing";
    //QString user = "copy";
    //QString password = "6269768";
    // QString dateBaseName = "property_manage";
    QString user = sysInfo->userName;
    QString password = sysInfo->passWord;
    QString dateBaseName = sysInfo->databaseName;
    mySql_db =QSqlDatabase::addDatabase("QMYSQL");
    mySql_db.setHostName(ip);
    mySql_db.setDatabaseName(dateBaseName);
    mySql_db.setUserName(user);
    mySql_db.setPassword(password);
    mySql_db.setPort(port);
}



void databaseClass::databaseinit(QString dbname)
{
   sqlite_db= QSqlDatabase::addDatabase("QSQLITE","ONE");
   sqlite_db.setDatabaseName(dbname);
   if(!QFile::exists(dbname))
   {
       if(!sqlite_db.open())
       {
           qDebug()<<"Unable to open sqlite database";
           return;
       }
       QStringList create_cmd;
       create_cmd.append(CREATE_INFO_VOICE);
       create_cmd.append(CREATE_INFO_RECORD);
       QSqlQuery *query = new QSqlQuery(sqlite_db);
       foreach(QString cmd,create_cmd)
       {
           if(!query->exec(cmd))
           {
               qDebug()<<"build sqlite error"<<query->lastError().text();
               return;
           }else{
              qDebug()<<"build sqlite success";
           }
       }
   }else{
       if(!sqlite_db.open())
       {
           qDebug()<<"Unable to open sqlite database";
       }
       else
       {
           qDebug()<<"open sqlite database sucsess";
       }
   }
}

bool databaseClass::controller_sn_infor(QString dev_no)
{
    QSqlQuery query(mySql_db);
    int nes;
    QString str=QObject::tr("select * from info_cabinet where controller_sn='%1' and cabinet_status='正常'").arg(dev_no);
    query.clear();
    query.prepare(str);
    query.exec();
    nes=query.next();
    if(nes==1)
    {
        //qDebug()<<"controller_sn_infor return true";
        query.clear();
        return true;
    }else{
       // qDebug()<<QString("info_cabinet %1 return false").arg(dev_no);
        query.clear();
        return false;
    }
}

bool databaseClass::mj_controller_sn_infor(QString dev_no,houseInfo *info)
{
    QSqlQuery query(mySql_db);
    int nes;
    QString str;
    //str=QObject::tr("select * from cfg_controller where controller_sn='%1' and enabled='Y'").arg(dev_no);
    str=QObject::tr("select community_id,root_id from info_door where sn='%1'").arg(dev_no);

    query.clear();
    query.prepare(str);
    query.exec();
    nes=query.next();
    if(nes==1)
    {
        //qDebug()<<"controller_sn_infor return true";
        info->community_id=query.value(0).toString();
        info->root_id=query.value(1).toString();
        query.clear();

        return true;
    }else{
       // qDebug()<<"controller_sn_infor return false"<<str<<query.lastError();
        info->community_id.clear();
        info->root_id.clear();
        query.clear();
        return false;
    }
}

bool  databaseClass::box_id_infor(QString controller_sn,QString card_sn,boxIdInfo *boxInfo)
{
    QSqlQuery query(mySql_db);
    int nes;
    QString str=QObject::tr("select id from info_cabinet where controller_sn='%1' and cabinet_status='正常'").arg(controller_sn);
    query.clear();
    query.prepare(str);
    query.exec();
    nes=query.next();
    if(nes==1)
    {
        QString cabined_id=query.value(0).toString();
        QSqlQuery query_uid(mySql_db);
        int res;
        QString str=QObject::tr("select uid from info_consumer where card_sn='%1' and card_status='正常' and cabinet_id='%2'").arg(card_sn).arg(cabined_id);
        query_uid.clear();
        query_uid.prepare(str);
        query_uid.exec();
        res=query_uid.next();
        if(res)
        {
            boxInfo->id=cabined_id;
            boxInfo->uid=query_uid.value(0).toString();
           // qDebug()<<"box_id_infor info_consumer return true"<<boxInfo->id<<boxInfo->uid;
            query.clear();
            query_uid.clear();
            return true;
        }else{
           // qDebug()<<"box_id_infor info_consumer uid return false"<<str;
            query.clear();
            query_uid.clear();
            return false;
        }
    }else{
      //  qDebug()<<"box_id_infor info_cabinet id return false"<<str;
        query.clear();
        return false;
    }
}

QString databaseClass::box_no_infor(int flag, QString id,QString uid)
{
    QSqlQuery query(mySql_db);
    QString box_no,str;
    box_no="";
    if(flag==0)
    {
        str=QObject::tr("select box_no from info_card_box where cabinet_id='%1' and uid='%2' and status='正常'").arg(id).arg(uid);
    }else if(flag==1)
    {
        str=QObject::tr("select box_no from info_card_box where cabinet_id='%1' and uid='%2' and status='正常' and down_flag=0").arg(id).arg(uid);
    }
    else{
        str=QObject::tr("select box_no from info_card_box where cabinet_id='%1' and uid='%2' and status !='正常' and down_flag=0").arg(id).arg(uid);
    }
    query.clear();
    query.prepare(str);
    query.exec();
    while(query.next())
    {
        if(box_no.isEmpty())
        {
            box_no=query.value(0).toString();
        }else{
            box_no.append(",").append(query.value(0).toString());
        }
    }
    if(box_no.contains(","))
    {
        box_no=box_no.replace(',','-');
    }
    //qDebug()<<"二/databaseClass::box_no_infor str="<<str<<" box_no="<<box_no;
    query.clear();
    return box_no;
}
//1 下载 /2 删除
bool  databaseClass::box_sn_infor(int flag,QString controller_sn,boxIdInfo *boxInfo)
{
    //bool nes;
    QString cabined_id;
    cabined_id=info_cabinet_select_id(controller_sn);
    if(!cabined_id.isEmpty())
    {
        QSqlQuery query_uid(mySql_db);
        QString box_no,str;
        int res;
        if(flag==1)
        {
        str=QObject::tr("select uid,card_sn from info_consumer where user_type=3 and card_status='正常' and cabinet_id='%1' and down_flag=0 order by id asc limit 1").arg(cabined_id);
        }else{
        str=QObject::tr("select uid,card_sn from info_consumer where user_type=3 and cabinet_id='%1' and down_flag=0 order by id asc limit 1").arg(cabined_id);
       }
       //qDebug()<<"str"<<str;
        query_uid.clear();
        query_uid.prepare(str);
        query_uid.exec();
        res=query_uid.next();
             if(res)
        {
                 boxInfo->id=cabined_id;
                 boxInfo->uid=query_uid.value(0).toString();
                 boxInfo->sn=query_uid.value(1).toString();
                 //qDebug()<<"一/box_sn_infor info_consumer return true"<<boxInfo->id<<boxInfo->uid<<boxInfo->sn;
                 //box_no=box_no_infor(2,boxInfo->id, boxInfo->uid);
                 box_no=box_no_infor(flag,boxInfo->id, boxInfo->uid);
                 query_uid.clear();
                 if(box_no.isEmpty())
            {
                boxInfo->id.clear();
                boxInfo->uid.clear();
                return false;
            }else{
                boxInfo->box= box_no;
                return true;
            }
        }else{
          //  qDebug()<<flag<<QString("box_sn_infor %1 info_consumer return false").arg(controller_sn);
            query_uid.clear();
            boxInfo->sn.clear();
            return false;
        }
    }else{
      //  qDebug()<<flag<<QString("box_sn_infor %1 info_cabinet return false").arg(controller_sn);
        boxInfo->sn.clear();
        return false;
    }
}

bool databaseClass::writeOpenLog(QString card_sn,QString cabinet_id,QString box_no,QString uid)
{
    QDateTime time = QDateTime::currentDateTime();
    QString cuttime = time.toString("yyyy-MM-dd hh:mm:ss");
    //QString pound_no = sysInfo->galleryNumber+time.toString("yyMMddhhmmsszzz");
    QSqlQuery query(mySql_db);
    query.clear();
    query.prepare("insert into record_open_box(card_sn,cabinet_id,box_no,open_time,created_at,uid) values (?,?,?,?,?,?)");
    query.bindValue(0,card_sn);
    query.bindValue(1,cabinet_id);
    query.bindValue(2,box_no);
    query.bindValue(3,cuttime);
    query.bindValue(4,cuttime);
    query.bindValue(5,uid);
    int  guet = query.exec();
    if(guet==1)
    {
        //qDebug()<<QObject::trUtf8("return writeOpenLog %1 %2 ture").arg(card_sn).arg(box_no);
        query.clear();
        return true;
    }else{
      //  qDebug()<<QObject::trUtf8("%1>>return writeOpenLog fasle").arg(cuttime)<<query.lastError().text();
        query.clear();
        return false;
    }
}

bool databaseClass::writeGpsLog(QString vehicle_sn,QString temprature,QString lng_gps,QString lat_gps)
{
    QDateTime time = QDateTime::currentDateTime();
    QString cuttime = time.toString("yyyy-MM-dd hh:mm:ss");
    //QString pound_no = sysInfo->galleryNumber+time.toString("yyMMddhhmmsszzz");
    QSqlQuery query(mySql_db);;
    query.clear();
    query.prepare("insert into record_vehicle_gps(vehicle_no,created_at,temperature,lng_gps,lat_gps) values (?,?,?,?,?)");
    query.bindValue(0,vehicle_sn);
    query.bindValue(1,cuttime);
    query.bindValue(2,temprature);
    query.bindValue(3,lng_gps);
    query.bindValue(4,lat_gps);
    int  guet = query.exec();
    if(guet==1)
    {
       // qDebug()<<QObject::trUtf8("%1>>return writeGpsLog 经度:%2 纬度:%3 ture").arg(cuttime).arg(lng_gps).arg(lat_gps);
        query.clear();
        return true;
    }else{
      //  qDebug()<<QObject::trUtf8("%1>>return writeGpsLog fasle").arg(cuttime)<<query.lastError().text();
        query.clear();
        return false;
    }
}

QString databaseClass::vehicle_infor(QString id)
{
    QSqlQuery query(mySql_db);
    QString vehicle_no;
    vehicle_no=id;
    QString str=QObject::tr("select vehicle_no from info_vehicle where GPS_dev_sn='%1' order by updated_at desc limit 1").arg(id);
    query.clear();
    query.prepare(str);
    query.exec();
    if(query.next())
    {
        vehicle_no=query.value(0).toString();
    }
   // qDebug()<<"str="<<str<<"*****vehicle_no="<<vehicle_no<<"*****";
    query.clear();
    return vehicle_no;
}

bool databaseClass::privilege_infor(QString dev_id,QString ic_code)
{
    QSqlQuery query(mySql_db);
    int nes;
    QString str=QObject::tr("select Begin_date,End_date from guard_record_auth_code_privilege where Auth_code='%1' and Dev_sn='%2' and status='正常'").arg(ic_code).arg(dev_id);
    query.clear();
    query.prepare(str);
    query.exec();
    nes=query.next();
    if(nes==1)
    {
        QString begin_date,end_date;
        begin_date=query.value(0).toString();
        end_date=query.value(1).toString();
        QDate curren_D,begin_D,end_D;
        curren_D=QDate::currentDate();
        begin_D=QDate::fromString(begin_date);
        end_D=QDate::fromString(end_date);
        if(curren_D.operator >=(begin_D) && curren_D.operator <=(end_D))
        {
            //  qDebug()<<"privilege_infor return true";
            query.clear();
            return true;
        }else{
            //  qDebug()<<"privilege_infor return false";
            query.clear();
            return false;
        }
    }else{
        //  qDebug()<<"privilege_infor return false";
        query.clear();
        return false;
    }
}


bool databaseClass::download_card(QString dev_no)
{
    QSqlQuery query(mySql_db);
    int nes;
    QString str=QObject::tr("select f_pri_synchronize('%1')").arg(dev_no);
    query.prepare(str);
    nes=query.exec();
    if(nes==1)
    {
       // qDebug()<<"download_card return true "<<str;
        query.clear();
        return true;
    }else{
       // qDebug()<<"databaseClass::download_card return false"<<query.lastError();
        query.clear();
        return false;
    }
}

bool databaseClass::consumer_info(QString Dev_sn,conInfo *consumerInfo)
{
    //QSqlQuery query_house(mySql_db);
    QSqlQuery query(mySql_db);
    bool nes;
    //bool res;
    QString str;
    //str=QObject::tr("SELECT aa.card_no,aa.end_date,aa.id,aa.voice_id,aa.download_flag from (select a.id,a.consumer_id,a.dev_sn,a.card_no,a.end_date,a.download_flag,b.voice_id,b.house_id from guard_record_consumer_privilege a,guard_info_consumer b where a.dev_sn='%1' and (a.download_flag='N' or a.download_flag='D') and a.consumer_id=b.id) aa LEFT JOIN guard_record_house_privilege bb on aa.dev_sn=bb.dev_sn order by aa.id asc limit 1").arg(Dev_sn);
    str=QString("select a.card_no,a.end_date,a.id,b.voice_id,a.download_flag from guard_record_consumer_privilege a left join guard_info_consumer b  on a.consumer_id=b.id where a.dev_sn='%1' and (a.download_flag='N' or a.download_flag='D') order by a.id asc limit 1").arg(Dev_sn);
    query.prepare(str);
    query.exec();
    nes=query.next();
    //qDebug()<<"str_id"<<str<<nes;
    if(nes)
    {
        QString card_ic_sn=query.value(0).toString();
        consumerInfo->end_ate=query.value(1).toString();
        consumerInfo->id=query.value(2).toString();
        consumerInfo->voice_id=query.value(3).toString();
        consumerInfo->down_flag=query.value(4).toString();
        query.clear();
        if(card_ic_sn.isEmpty() || consumerInfo->end_ate.isEmpty())
        {
            consumerInfo->ic_sn="NULL";
            //qDebug()<<flag<<Dev_sn<<"databaseClass::guard_info_consumer return false"<<card_ic_sn<<consumerInfo->end_ate;
            return false;
        }else{
            consumerInfo->ic_sn=card_ic_sn;
            if(consumerInfo->voice_id.isEmpty() || consumerInfo->voice_id=="0")
            {
                consumerInfo->voice_id=QString::number(1);
            }
            //qDebug()<<flag<<Dev_sn<<"databaseClass::guard_info_consumer return true"<<consumerInfo->id<<consumerInfo->voice_id<<consumerInfo->down_flag;
            return true;
        }
    }else{
        query.clear();
        consumerInfo->ic_sn.clear();
        //qDebug()<<flag<<Dev_sn<<"databaseClass::guard_info_consumer return false";
        return false;
    }
 }

bool databaseClass::consumer_change(QString flag,QString Dev_sn,QString card_sn)
{
    QSqlQuery query(mySql_db);
    bool res;
    QString str;
    if(flag=="1")
    {
        str = QString("update guard_record_consumer_privilege set download_flag='Y' where dev_sn='%1' and card_no='%2' and download_flag='N'").arg(Dev_sn).arg(card_sn);
    }
    else if(flag=="2")
    {
        str = QObject::tr("delete from guard_record_consumer_privilege where dev_sn='%1'and card_no='%2' and download_flag='D'").arg(Dev_sn).arg(card_sn);
        //str= QObject::tr("update  guard_info_consumer a,guard_record_house_privilege b set a.down_flag='3' WHERE a.house_id=b.house_id  and   b.dev_sn='%1' and a.identify_no='%2'").arg(Dev_sn).arg(card_sn);
    }
    else if(flag=="3")
    {
        str = QString("update guard_record_consumer_privilege set download_flag='Y' where dev_sn='%1' and id='%2' and download_flag='N'").arg(Dev_sn).arg(card_sn);
    }
    else if(flag=="4")
    {
        str = QObject::tr("delete from guard_record_consumer_privilege where dev_sn='%1'and id='%2' and download_flag='D'").arg(Dev_sn).arg(card_sn);
    }
    query.prepare(str);
    res=query.exec();
    query.clear();
    //qDebug()<<str;
    return res;
}

bool databaseClass::house_change(QString flag,QString dev_sn,QString house_id)
{
    QSqlQuery query(mySql_db);
    bool res;
    QString str;
    if(flag=="1")
    {
        str = QObject::tr("update guard_record_house_privilege set Download_flag='Y' where dev_sn='%1' and  house_id='%2'  and Download_flag='N'").arg(dev_sn).arg(house_id);
        query.prepare(str);
        //query.bindValue(":dev_no",Dev_sn);
        //query.bindValue(":card_sn",card_sn);
    }
    if(flag=="2")
    {
        //str = QObject::tr("delete from guard_record_consumer_privilege where Dev_sn='%1'and Card_no='%2' and Download_flag='D'").arg(Dev_sn).arg(card_sn);
        str = QObject::tr("update guard_record_house_privilege set Download_flag='T' where dev_sn='%1' and  house_id='%2' and Download_flag='D'").arg(dev_sn).arg(house_id);
        query.prepare(str);
    }
    res=query.exec();
    query.clear();
    return res;
}

bool databaseClass::voice_change(QSqlDatabase *db,QString flag,QString dev_sn,QString id)
{
    QSqlQuery query(*db);
    bool res;
    QString str;
    if(flag=="1")
    {
        str = QObject::tr("update voice_record set download_flag='Y' where code='%1' and dev_sn='%2'").arg(id).arg(dev_sn);
    }else if(flag=="2"){
        str = QObject::tr("delete voice_record  where code='%1' and dev_sn='%2'").arg(id).arg(dev_sn);
    }else{
        str = QObject::tr("update voice_record set download_flag='E' where id='%1' and dev_sn='%2'").arg(id).arg(dev_sn);
    }
    //qDebug()<<"databaseClass::voice_change"<<flag<<str;
    query.prepare(str);
    res=query.exec();
    query.clear();
    return res;
}

bool databaseClass::box_no_change(int flag,QString controller_sn,QString card_sn)
{
    QSqlQuery query(mySql_db);
    bool res;
    QString str,uid_str,sta_str;
    str=QObject::tr("select id from info_cabinet where controller_sn='%1' and cabinet_status='正常'").arg(controller_sn);
    query.clear();

    query.prepare(str);
    query.exec();
    res=query.next();
    if(res)
    {
        QString cabined_id;
        cabined_id=query.value(0).toString();
        QSqlQuery query_uid(mySql_db);
        //        if(flag==1)
        //        {
        //            str = QObject::tr("update info_consumer set down_flag=1 where user_type=3 and card_status='正常' and cabinet_id='%1' and card_sn='%2' and down_flag=0").arg(cabined_id).arg(card_sn);
        //                 uid_str=QObject::tr("select uid from info_consumer where user_type=3 and card_status='正常' and cabinet_id='%1' and card_sn='%2' order by updated_at asc limit 1").arg(cabined_id).arg(card_sn);
        //        }else{
        //            str = QObject::tr("update info_consumer set down_flag=1 where user_type=3 and card_status !='正常' and cabinet_id='%1' and card_sn='%2' and down_flag=0").arg(cabined_id).arg(card_sn);
        //                 uid_str=QObject::tr("select uid from info_consumer where user_type=3 and card_status !='正常' and cabinet_id='%1' and card_sn='%2' order by updated_at asc limit 1").arg(cabined_id).arg(card_sn);
        // }
        str = QObject::tr("update info_consumer set down_flag=1 where user_type=3 and cabinet_id='%1' and card_sn='%2' and down_flag=0").arg(cabined_id).arg(card_sn);
        //qDebug()<<flag<<"databaseClass::box_no_change info_consumer"<<cabined_id<<card_sn;
        query.clear();
        query.prepare(str);
        query.exec();
        query.clear();
        if(flag > 2 )
        {
        return false;
        }
        uid_str=QObject::tr("select uid from info_consumer where user_type=3  and cabinet_id='%1' and card_sn='%2' order by id asc limit 1").arg(cabined_id).arg(card_sn);
        query_uid.clear();
        query_uid.prepare(uid_str);
        query_uid.exec();
        res=query_uid.next();
        if(res)
        {
            QString box_uid;
            box_uid=query_uid.value(0).toString();
            //str = QObject::tr("update info_milk_box set down_flag=1 where cabinet_id=:CabinetId and uid=:Uid");
            if(flag==1)
            {
                str = QObject::tr("update info_card_box set down_flag=1 where cabinet_id=:CabinetId and uid=:Uid and status ='正常'");
            }else{
                sta_str=QObject::tr("update info_card_box set down_flag=0 where cabinet_id='%1' and uid='%2' and status ='正常'").arg(cabined_id).arg(box_uid);
                str = QObject::tr("update info_card_box set down_flag=1 where cabinet_id=:CabinetId and uid=:Uid and status!='正常'");
                query.clear();
                query.prepare(sta_str);
                query.exec();
                query.clear();
            }
            query_uid.clear();
            query_uid.prepare(str);
            query_uid.bindValue(":CabinetId",cabined_id);
            query_uid.bindValue(":Uid",box_uid);
            query_uid.exec();
            query_uid.clear();
            //qDebug()<<flag<<"databaseClass::box_no_change info_card_box true"<<str<<cabined_id<<box_uid;
            //qDebug()<<flag<<"databaseClass::box_no_change info_card_box sta_str"<<sta_str;
            //qDebug()<<flag<<"databaseClass::box_no_change info_card_box str"<<str;
        }
        //else{
        // qDebug()<<flag<<"databaseClass::box_no_change info_consumer false"<<query_uid.lastError().text();
        //}
    }
    //else{
   // qDebug()<<flag<<QString("databaseClass::box_no_change %1 info_cabinet  %2 false").arg(controller_sn).arg(card_sn);
    //}
    return res;
}


bool databaseClass::record_in_out(QString pass_no,QString controller_sn,QString ic_sn,QString pass)
{
    QSqlQuery query(mySql_db);
    QString str,cuttime,consumer,device_name;
    cuttime=QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss");
    consumer="";
    device_name="";
    //QString ic_no=QString::number(ic_sn.toInt());
    str=QObject::tr("select name from guard_info_consumer where identify_no='%1'").arg(ic_sn);
    query.prepare(str);
    query.exec();
    if(query.next())
    {
        consumer=query.value(0).toString().trimmed();
    }
    //qDebug()<<"str="<<str<<pass<<controller_sn<<ic_sn<<pass;
    query.clear();
    str=QObject::tr("select controller_name from guard_info_group_access_permissions_detail where controller_sn='%1' limit 1").arg(controller_sn);
    query.prepare(str);
    query.exec();
    if(query.next())
    {
        device_name=query.value(0).toString().trimmed();
    }
    houseInfo Hinfo;
    mj_controller_sn_infor(controller_sn,&Hinfo);

    query.clear();
    query.prepare("insert into guard_record_access(pass_no,ic_sn,consumename,passtype,controllersn,passtime,direction,device_name,community_id,root_id) values (?,?,?,?,?,?,?,?,?,?)");
    query.bindValue(0,pass_no);
    query.bindValue(1,ic_sn);
    query.bindValue(2,consumer);
    query.bindValue(3,"刷卡");
    query.bindValue(4,controller_sn);
    query.bindValue(5,cuttime);
    query.bindValue(6,pass);
    query.bindValue(7,device_name);
    query.bindValue(8,Hinfo.community_id);
    query.bindValue(9,Hinfo.root_id);
    int  guet = query.exec();
    if(guet==1)
    {
      // qDebug()<<QObject::trUtf8("databaseClass::record_in_out 设备号:%1 卡号:%2 ture").arg(controller_sn).arg(ic_sn);
        query.clear();
        return true;
    }else{
     // qDebug()<<QObject::trUtf8("databaseClass::record_in_out fasle")<<query.lastError().text();
        query.clear();
        return false;
    }
}


bool databaseClass::cfg_controller_update_heart(QString Dev_sn)
{
    QSqlQuery query(mySql_db);
    bool res;
    QString str;
    QString cuttime=QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss");
   //str = QObject::tr("update cfg_controller set heart_time='%1' where controller_sn='%2'").arg(cuttime).arg(Dev_sn);
    str = QObject::tr("update info_door set heart_time='%1' where sn='%2'").arg(cuttime).arg(Dev_sn);

    query.prepare(str);
    res=query.exec();
    query.clear();
    return res;
}

bool databaseClass::info_cabinet_update_heart(QString Dev_sn)
{
    QSqlQuery query(mySql_db);
    bool res;
    QString str;
    QString cuttime=QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss");
    str = QObject::tr("update info_cabinet set heartbeat_time='%1' where controller_sn='%2'").arg(cuttime).arg(Dev_sn);
    query.prepare(str);
    res=query.exec();
    query.clear();
    return res;
}

QString databaseClass::info_cabinet_select_id(QString controller_sn)
{
    QSqlQuery query(mySql_db);
    bool nes;
    QString cabined_id;
    cabined_id="";
    QString str=QObject::tr("select id from info_cabinet where controller_sn='%1' and cabinet_status='正常'").arg(controller_sn);
    query.clear();
    query.prepare(str);
    query.exec();
    nes=query.next();
    if(nes)
    {
        cabined_id=query.value(0).toString();
    }
    //qDebug()<<"databaseClass::info_cabinet_select_id"<<cabined_id;
    return cabined_id;
 }

bool databaseClass::info_consumer_update_downFlag(int flag,QString controller_sn)
{
    QString cabinet_id;
    bool nes;
    cabinet_id=info_cabinet_select_id(controller_sn);
    if(cabinet_id.isEmpty())
    {
       // qDebug()<<"databaseClass::info_consumer_update_downFlag=cabinet_id.isEmpty";
        return false;
    }
    nes=info_consumer_select_downFlag(cabinet_id);
    if(nes)
    {
       // qDebug()<<"databaseClass::info_consumer_update_downFlag=info_consumer_select_downFlag"<<nes;
        return false;
    }
    QString str;
    QSqlQuery query(mySql_db);
    bool res;
    if(flag==1)
    {
        str = QObject::tr("update info_consumer set down_flag=0 where cabinet_id='%1' and card_status='正常'").arg(cabinet_id);
    }else{
        str = QObject::tr("update info_consumer set down_flag=0 where cabinet_id='%1'").arg(cabinet_id);
    }
    query.prepare(str);
    res=query.exec();
    query.clear();
    return res;
}

bool databaseClass::info_consumer_check_downFlag(int flag,QString controller_sn)
{
    QString cabinet_id;
    bool nes;
    cabinet_id=info_cabinet_select_id(controller_sn);
    if(cabinet_id.isEmpty())
    {
      //  qDebug()<<"databaseClass::info_consumer_update_downFlag=cabinet_id.isEmpty";
        return false;
    }
    nes=info_consumer_select_downFlag(cabinet_id);
    if(nes)
    {
      //  qDebug()<<"databaseClass::info_consumer_update_downFlag=info_consumer_select_downFlag"<<nes;
        return false;
    }
    QString str;
    QSqlQuery query(mySql_db);
    QSqlQuery query_box(mySql_db);
    //bool res;
    if(flag==1)
    {
        str=QObject::tr("select uid from info_consumer where cabinet_id='%1' and card_status='正常' order by id asc").arg(cabinet_id);
    }
    else{
        str=QObject::tr("select uid from info_consumer where cabinet_id='%1' order by id asc").arg(cabinet_id);
    }
    QString box_no,uid;
    query.clear();
    query.prepare(str);
    query.exec();
    while(query.next())
    {
        uid=query.value(0).toString();
        box_no=box_no_infor(flag,cabinet_id,uid);
        if(!box_no.isEmpty())
        {
            if(flag==1)
            {
                str = QObject::tr("update info_consumer set down_flag=0 where cabinet_id='%1' and card_status='正常' and uid='%2'").arg(cabinet_id).arg(uid);
            }else{
                str = QObject::tr("update info_consumer set down_flag=0 where cabinet_id='%1' and uid='%2'").arg(cabinet_id).arg(uid);
            }
            query_box.prepare(str);
            query_box.exec();
            query_box.clear();
        }
    }
    return true;
}

bool databaseClass::info_consumer_select_downFlag(QString cabinet)
{
    QSqlQuery query(mySql_db);
    int nes;
    QString str=QObject::tr("select uid from info_consumer where cabinet_id='%1' and down_flag=0").arg(cabinet);
    query.clear();
    query.prepare(str);
    query.exec();
    nes=query.next();
    if(nes==1)
    {
        query.clear();
        return true;
    }else{
        query.clear();
        return false;
    }
}

void  databaseClass::mj_Consumer_Change(QString flag,QString dev_no)
{
    QSqlQuery query(mySql_db);
    QSqlQuery query_house(mySql_db);
    QString houseStr,id;
    QString str;
    if(flag==QString::number(1))
    {
        str=QObject::tr("SELECT a.id FROM guard_info_consumer a,guard_record_house_privilege b WHERE a.house_id=b.house_id and b.download_flag='N' and b.dev_sn='%1' ").arg(dev_no);
    }else{
        str=QObject::tr("SELECT a.id FROM guard_info_consumer a,guard_record_house_privilege b WHERE a.house_id=b.house_id and b.download_flag='D' and b.dev_sn='%1' ").arg(dev_no);
    }
    query.clear();
    query.prepare(str);
    query.exec();
    while(query.next())
    {
        id=query.value(0).toString();
        if(flag=="1")
        {
            houseStr=QObject::tr("update guard_info_consumer set download_flag='0' where id='%1'").arg(id);
        }else{
            houseStr=QObject::tr("update guard_info_consumer set download_flag='2' where id='%1'").arg(id);
        }
        query_house.exec(houseStr);
        query_house.clear();
    }
    query.clear();
}

void databaseClass::mj_houseId_Change(QString flag,QString dev_sn)
{
    QSqlQuery query(mySql_db);
    QSqlQuery query_consumer(mySql_db);
    QString str,str_con;
    QString house_id;
    if(flag=="1")
    {
        //str=QObject::tr("select count(identify_no) from guard_info_consumer where house_id='%1' and  down_flag=0").arg(house_id);
        str=QObject::tr("SELECT distinct(b.house_id) FROM guard_info_consumer a,guard_record_house_privilege b WHERE a.house_id=b.house_id  and a.down_flag='0' and b.dev_sn='%1'").arg(dev_sn);

    }else{
        //str=QObject::tr("select count(identify_no) from guard_info_consumer where house_id='%1' and  down_flag=2").arg(house_id);
        str=QObject::tr("SELECT distinct(b.house_id) FROM guard_info_consumer a,guard_record_house_privilege b WHERE a.house_id=b.house_id  and a.down_flag='2' and b.dev_sn='%1'").arg(dev_sn);
    }
    query.clear();
    query.prepare(str);
    query.exec();
   // qDebug()<<"str="<<str;
    while(query.next())
    {
        house_id=query.value(0).toString();
        if(flag=="1")
        {
            str_con=QObject::tr("update guard_record_house_privilege set download_flag='N' where house_id='%1'").arg(house_id);
        }else{
            str_con=QObject::tr("update guard_record_house_privilege set download_flag='D' where house_id='%1'").arg(house_id);
        }
        query_consumer.prepare(str_con);
        query_consumer.exec();
        query_consumer.clear();
        //qDebug()<<"house_id"<<house_id<<str_con;
    }
    query.clear();
}

bool databaseClass::info_voice_downFlag(QSqlDatabase *db,QString flag,QString dev_sn,QString code)
{
    QSqlQuery query(*db);
    int nes;
    QString str;
    if(flag=="1")
    {
        str=QObject::tr("select voice from guard_dict_voice where code='%1'").arg(code);
    }else if(flag=="2")
    {
      //str =QObject::tr("select id from voice_record where dev_sn='%1' and (download_flag='N' or download_flag='D')").arg(dev_sn);
        str =QObject::tr("select id from record_door_voice where dev_sn='%1' and (download_flag='N' or download_flag='D')").arg(dev_sn);
    }else{
       //str =QObject::tr("select id from voice_record where dev_sn='%1' and code='%2'").arg(dev_sn).arg(code);
        str =QObject::tr("select id from record_door_voice where dev_sn='%1' and voice_code='%2'").arg(dev_sn).arg(code);

    }
    //qDebug()<<"databaseClass::info_voice_downFlag"<<flag<<str;
    query.clear();
    query.prepare(str);
    query.exec();
    nes=query.next();
    if(nes==1)
    {
        query.clear();
        return true;
    }else{
        query.clear();
        return false;
    }
}

bool  databaseClass::voice_infor(QSqlDatabase *db,QString dev_no,voiceInfo *info)
{
    bool res;
    QSqlQuery query(*db);
    QString str;
   //str=QObject::tr("select a.id,a.code,b.voice,a.download_flag from guard_dict_voice b,voice_record a where (a.download_flag='N' or a.download_flag='D') and a.code=b.code and a.dev_sn='%1' order by a.id asc limit 1").arg(dev_no);
    str=QObject::tr("select a.id,a.voice_code,b.voice,a.download_flag from record_door_voice a,guard_dict_voice b where (a.download_flag='N' or a.download_flag='D') and a.voice_id=b.id and a.dev_sn='%1' ORDER BY a.updated_at").arg(dev_no);
    query.clear();
    query.prepare(str);
    query.exec();
    res=query.next();
    if(res)
    {
        info->id=query.value(0).toString();
        info->code=query.value(1).toString();
        info->voice=query.value(2).toString();
        info->open_flag=query.value(3).toString();
        query.clear();
        if(info->id.isEmpty() || info->voice.isEmpty())
        {
         //   qDebug()<<"databaseClass voice_infor return false"<<info->id<<info->voice;
            return false;
        }else{
           //qDebug()<<"databaseClass voice_infor return true";
            return true;
        }
    }else{
        info->id.clear();
        info->code.clear();
        info->voice.clear();
        info->open_flag.clear();
     //   qDebug()<<"databaseClass voice_infor return false";
        query.clear();
        return false;
    }
}

void databaseClass::voiceInfo_down(QSqlDatabase *Msql_db,QSqlDatabase *Lsql_db)
{
    QSqlQuery query(*Msql_db);
    QSqlQuery query_consumer(*Lsql_db);
    QSqlQuery query_voice(*Lsql_db);
    QString str,code,voice,down_flag,str_con;
    bool res,nes;
    str=QObject::tr("select code,voice,download_flag from guard_dict_voice where (download_flag='N' or download_flag='D') order by id asc");
    query.clear();
    query.prepare(str);
    query.exec();
    while(query.next())
    {
        code=query.value(0).toString();
        voice=query.value(1).toString();
        down_flag=query.value(2).toString();
        res=info_voice_downFlag(Lsql_db,"1","",code);
        if(res)
        {
            if(down_flag=="N")
            {
            str_con=QObject::tr("update guard_dict_voice set voice='%1',download_flag='N' where code='%3'").arg(voice).arg(code);
            }else{
            str_con=QObject::tr("delete guard_dict_voice  where code='%1'").arg(code);
            }
            query_consumer.prepare(str_con);
            nes=query_consumer.exec();
            if(nes)
            {
                voiceDownFlag_change(Msql_db,down_flag,code);
                str_con=QObject::tr("update voice_record set download_flag='%1' where code='%2'").arg(down_flag).arg(code);
                query_voice.prepare(str_con);
                query_voice.exec();
                query_voice.clear();
               // qDebug()<<"更新记录表"<<str_con;
            }
            query_consumer.clear();
        }else{
            query_consumer.prepare("insert into guard_dict_voice(code,voice,download_flag) values (?,?,?)");
            query_consumer.bindValue(0,code);
            query_consumer.bindValue(1,voice);
            query_consumer.bindValue(2,down_flag);
            nes=query_consumer.exec();
            if(nes)
            {
                voiceDownFlag_change(Msql_db,down_flag,code);
            }
            query_consumer.clear();
        }
    }
    query.clear();
}

void databaseClass::voiceDownFlag_change(QSqlDatabase *Msql_db,QString flag,QString code)
{
    QSqlQuery query(*Msql_db);
    QString str;
    if(flag=="N")
    {
        str=QObject::tr("update guard_dict_voice set  download_flag='Y' where code='%1' and download_flag='N'").arg(code);
    }else{
        str=QObject::tr("delete guard_dict_voice  where code='%1' and download_flag='D'").arg(code);
    }
    query.prepare(str);
    query.exec();
    query.clear();
}

void databaseClass::voiceInfo_Find(QSqlDatabase *Lsql_db,QString dev_no)
{
    QSqlQuery query(*Lsql_db);
    QSqlQuery query_voice(*Lsql_db);
    QString str,code,voice,down_flag;
    bool res;
    str=QObject::tr("select code,voice,download_flag from guard_dict_voice  where download_flag='N'  order by id asc");
    query.clear();
    query.prepare(str);
    query.exec();
    while(query.next())
    {
        code=query.value(0).toString();
        voice=query.value(1).toString();
        down_flag=query.value(2).toString();
        res=info_voice_downFlag(Lsql_db,"3",dev_no,code);
        if(!res)
        {
            query_voice.prepare("insert into voice_record(code,dev_sn,download_flag) values (?,?,?)");
            query_voice.bindValue(0,code);
            query_voice.bindValue(1,dev_no);
            query_voice.bindValue(2,down_flag);
            query_voice.exec();
            query_voice.clear();
        }
    }
    query.clear();
}

